using DBUtil.MetaData;
using DotNetCommon;
using DotNetCommon.Extensions;
using System;
using System.Linq;
using System.Threading.Tasks;

namespace DBUtil.Provider.SqlServer;

/// <summary>
/// SqlServer数据库管理对象
/// </summary>
/// <remarks>
/// 根据DBAccess创建DBManage
/// </remarks>
/// <param name="db"></param>
public partial class SqlServerManage(DBAccess db) : DBManage(db)
{
    #region 修改元数据
    /// <inheritdoc />
    public override string RenameTableSql(string oldTablePureName, string newTablePureName, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(oldTablePureName);
        AssertUtil.NotNullOrWhiteSpace(newTablePureName);
        //重命名语句不会改变所属模式,所以: sp_rename 'dbo.test','tt' 而不是: sp_rename 'dbo.test','dbo.tt',也不是 sp_rename 'dbo.test','[tt]'
        if (schemaPureName.IsNullOrWhiteSpace())
        {
            oldTablePureName = db.AddQuote(oldTablePureName);
            newTablePureName = db.AddQuote(newTablePureName);
        }
        else
        {
            oldTablePureName = $"{db.AddQuote(schemaPureName)}.{db.AddQuote(oldTablePureName)}";
            newTablePureName = $"{db.AddQuote(newTablePureName)}";
        }
        var sql =
            $"""
            -- 重命名语句不会改变所属模式,所以: sp_rename 'dbo.test','tt' 而不是: sp_rename 'dbo.test','dbo.tt',也不是 sp_rename 'dbo.test','[tt]'
            -- 参考: https://learn.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-ver17
            exec sp_rename {oldTablePureName},{newTablePureName}
            """;
        return sql;
    }

    /// <inheritdoc />
    public override string RenameColumnSql(string tablePureName, string oldPureName, string newPureName, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tablePureName);
        AssertUtil.NotNullOrWhiteSpace(oldPureName);
        AssertUtil.NotNullOrWhiteSpace(newPureName);

        oldPureName = db.AddQuote(oldPureName);
        newPureName = db.AddQuote(newPureName);

        if (schemaPureName.IsNullOrWhiteSpace())
        {
            oldPureName = $"{tablePureName}.{oldPureName}";
        }
        else
        {
            oldPureName = $"{db.AddQuote(schemaPureName)}.{tablePureName}.{oldPureName}";
        }

        var sql =
            $"""
            -- 参考: https://learn.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-ver17
            exec sp_rename {oldPureName}, {newPureName}, 'column';
            """;
        return sql;
    }

    /// <inheritdoc />
    public override string UpdateTableDescriptionSql(string tablePureName, string description, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tablePureName);
        var sql =
                $"""
                -- 传入 sp_updateextendedproperty 的 level0name 不能是函数调用
                DECLARE @CurrentSchema NVARCHAR(128) = {(schemaPureName.IsNullOrWhiteSpace() ? "SCHEMA_NAME()" : db.ConvertToSqlSeg(schemaPureName).Data)};
                -- 检查表是否已存在 MS_Description 属性 不检查直接调用 sp_updateextendedproperty 会报错
                IF EXISTS (
                    SELECT 1 
                    FROM sys.extended_properties 
                    WHERE name = N'MS_Description'
                      -- OBJECT_ID(N'testtable') or OBJECT_ID(N'[dbo].[testtable]')
                      AND major_id = OBJECT_ID({db.ConvertToSqlSeg(schemaPureName.IsNullOrWhiteSpace() ? tablePureName : (db.AddQuote(schemaPureName) + "." + db.AddQuote(tablePureName))).Data})
                      AND minor_id = 0  -- 表级属性（ minor_id=0 表示表本身）
                )
                BEGIN
                    -- 属性存在，更新
                    EXEC sp_updateextendedproperty 
                        @name = N'MS_Description',
                        @value = {db.ConvertToSqlSeg(description).Data},
                        @level0type = N'SCHEMA', @level0name = @CurrentSchema,
                        @level1type = N'TABLE', @level1name = {db.ConvertToSqlSeg(tablePureName).Data};
                END
                ELSE
                BEGIN
                    -- 属性不存在，添加
                    EXEC sp_addextendedproperty 
                        @name = N'MS_Description',
                        @value = {db.ConvertToSqlSeg(description).Data},
                        @level0type = N'SCHEMA', @level0name = @CurrentSchema,
                        @level1type = N'TABLE', @level1name = {db.ConvertToSqlSeg(tablePureName).Data};
                END
                -- 另外: 下面一行也可以用来查询表的注释
                -- SELECT * FROM fn_listextendedproperty ('MS_Description', 'schema', 'dbo', 'table', 'testtbl',null,null)
                -- 另外: 将表注释设为null不会有其他的出发逻辑, 纠正给正常的列设置一样
                """;
        return sql;
    }

    /// <inheritdoc />
    public override string DropTableIfExistSql(string tablePureName, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tablePureName);
        var sql = $"""
            -- 先检查表是否存在，存在则删除
            -- OBJECT_ID(N'testtable') or OBJECT_ID(N'[dbo].[testtable]')
            IF OBJECT_ID({(schemaPureName.IsNullOrWhiteSpace() ? db.ConvertToSqlSeg(tablePureName).Data : db.ConvertToSqlSeg(db.AddQuote(schemaPureName) + "." + db.AddQuote(tablePureName)))}, N'U') IS NOT NULL
            DROP TABLE {(schemaPureName.IsNullOrWhiteSpace() ? db.AddQuote(tablePureName) : (db.AddQuote(schemaPureName) + db.AddQuote(tablePureName)))};
            """;
        return sql;
    }

    /// <inheritdoc />
    public override string DropColumnSql(string tablePureName, string columnPureName, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tablePureName);
        AssertUtil.NotNullOrWhiteSpace(columnPureName);
        if (schemaPureName.IsNullOrWhiteSpace())
        {
            return $"alter table {db.AddQuote(tablePureName)} drop {db.AddQuote(columnPureName)}";
        }
        else
        {
            return $"alter table {db.AddQuote(schemaPureName)}.{db.AddQuote(tablePureName)} drop {db.AddQuote(columnPureName)}";
        }
    }

    /// <inheritdoc />
    public override string DropTriggerIfExistSql(string triggerPureName, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(triggerPureName);
        var sql = $"""
            -- 先检查触发器是否存在，存在则删除
            -- OBJECT_ID(N'trigger_name') or OBJECT_ID(N'[dbo].[trigger_name]')
            IF OBJECT_ID({(schemaPureName.IsNullOrWhiteSpace() ? db.ConvertToSqlSeg(triggerPureName).Data : db.ConvertToSqlSeg(db.AddQuote(schemaPureName) + "." + db.AddQuote(triggerPureName)))}, N'TR') IS NOT NULL
            DROP TRIGGER {(schemaPureName.IsNullOrWhiteSpace() ? db.AddQuote(triggerPureName) : (db.AddQuote(schemaPureName) + db.AddQuote(triggerPureName)))};
            """;
        return sql;
    }

    /// <inheritdoc />
    public override string DropProcedureIfExistSql(string procPureName, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(procPureName);
        var sql = $"""
            -- 先检查存储过程是否存在，存在则删除
            -- OBJECT_ID(N'trigger_name') or OBJECT_ID(N'[dbo].[trigger_name]')
            IF OBJECT_ID({(schemaPureName.IsNullOrWhiteSpace() ? db.ConvertToSqlSeg(procPureName).Data : db.ConvertToSqlSeg(db.AddQuote(schemaPureName) + "." + db.AddQuote(procPureName)))}, N'P') IS NOT NULL
            DROP TRIGGER {(schemaPureName.IsNullOrWhiteSpace() ? db.AddQuote(procPureName) : (db.AddQuote(schemaPureName) + db.AddQuote(procPureName)))};
            """;
        return sql;
    }

    /// <inheritdoc />
    public override async Task<string> UpdateColumnDescriptionSql(string tablePureName, string colPureName, string description, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tablePureName);
        AssertUtil.NotNullOrWhiteSpace(colPureName);
        var sql =
                $"""
                -- 传入 sp_updateextendedproperty 的 level0name 不能是函数调用
                DECLARE @CurrentSchema NVARCHAR(128) = {(schemaPureName.IsNullOrWhiteSpace() ? "SCHEMA_NAME()" : db.ConvertToSqlSeg(schemaPureName).Data)};
                -- 检查表是否已存在 MS_Description 属性 不检查直接调用 sp_updateextendedproperty 会报错
                IF EXISTS (
                    SELECT * FROM fn_listextendedproperty ('MS_Description', 'schema', {(schemaPureName.IsNullOrWhiteSpace() ? "SCHEMA_NAME()" : db.ConvertToSqlSeg(schemaPureName).Data)}, 'table', {db.ConvertToSqlSeg(tablePureName).Data},'column',{db.ConvertToSqlSeg(colPureName)})
                )
                BEGIN
                    -- 属性存在，更新
                    EXEC sp_updateextendedproperty 
                        @name = N'MS_Description',
                        @value = {db.ConvertToSqlSeg(description).Data},
                        @level0type = N'SCHEMA', @level0name = @CurrentSchema,
                        @level1type = N'TABLE', @level1name = {db.ConvertToSqlSeg(tablePureName).Data}
                        @level2type = N'COLUMN', @level2name = {db.ConvertToSqlSeg(colPureName).Data};
                END
                ELSE
                BEGIN
                    -- 属性不存在，添加
                    EXEC sp_addextendedproperty 
                        @name = N'MS_Description',
                        @value = {db.ConvertToSqlSeg(description).Data},
                        @level0type = N'SCHEMA', @level0name = @CurrentSchema,
                        @level1type = N'TABLE', @level1name = {db.ConvertToSqlSeg(tablePureName).Data}
                        @level2type = N'COLUMN', @level2name = {db.ConvertToSqlSeg(colPureName).Data};
                END
                -- 另外: 将表注释设为null不会有其他的出发逻辑, 纠正给正常的列设置一样
                """;
        return sql;
    }

    /// <inheritdoc />
    public override string AddColumnSql(string tablePureName, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tablePureName);
        if (schemaPureName.IsNullOrWhiteSpace())
        {
            tablePureName = db.AddQuote(tablePureName);
        }
        else
        {
            tablePureName = $"{db.AddQuote(schemaPureName)}.{db.AddQuote(tablePureName)}";
        }
        var sql = $@"-- 参考: https://learn.microsoft.com/zh-cn/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver17
alter table {tablePureName} add column
ColumnName
--    int -- 数据类型
--    not null -- 是否可为空 
--    unique -- 是否唯一
--    identity(1,1) -- 是否自增
--    default 0 -- 默认值
--    collate Chinese_PRC_CS_AS,
ColumnName2 -- 可以添加多列
--    int -- 数据类型
--    not null -- 是否可为空 
--    unique -- 是否唯一
--    identity(1,1) -- 是否自增
--    default 0 -- 默认值
--    collate Chinese_PRC_CS_AS
ColumnName2 -- 添加计算列
--    DECIMAL(18,2) -- 数据类型
--    AS (Quantity * UnitPrice);

-- 可以选择添加列注释
-- DECLARE @CurrentSchema NVARCHAR(128) = {(schemaPureName.IsNullOrWhiteSpace() ? "SCHEMA_NAME()" : db.ConvertToSqlSeg(schemaPureName).Data)};
-- EXEC sp_addextendedproperty 
--     @name = N'MS_Description',
--     @value = N'列注释内容',
--     @level0type = N'SCHEMA',
--     @level0name = @CurrentSchema,
--     @level1type = N'TABLE',
--     @level1name = {db.ConvertToSqlSeg(tablePureName).Data},
--     @level2type = N'COLUMN',
--     @level2name = N'Age';    -- 刚添加的列名
";
        return sql;
    }

    /// <inheritdoc />
    public override async Task<string> UpdateColumnNullAbleSql(string tablePureName, string colPureName, bool isNullAble, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tablePureName);
        AssertUtil.NotNullOrWhiteSpace(colPureName);

        var column = (await GetColumnsAsync(tablePureName, schemaPureName, colPureName)).FirstOrDefault();
        if (column == null) throw new Exception($"没有在表 {tablePureName} 中找到列 {colPureName}!");

        if (column.IsPrimaryKey && isNullAble == true) throw new Exception("主键不允许设置为空!");
        if (column.IsIdentity && isNullAble == true) throw new Exception("自增不允许设置为空!");
        column.IsNullAble = isNullAble;
        var fullName = schemaPureName.IsNotNullOrWhiteSpace() ? (db.AddQuote(schemaPureName) + "." + db.AddQuote(tablePureName)) : db.AddQuote(tablePureName);
        var sql =
            $"""
            -- 比如: alter table test alter column name varchar(50) not null
            alter table {fullName} alter column {db.AddQuote(column.Name)} {column.TypeString} {(isNullAble ? "null" : "not null")}
            """;
        return sql;
    }

    #region FixColumnName FixedTableSchema
    /// <summary>
    /// 如:
    /// <list type="bullet">
    /// <item>FixTableOrViewName("DBO", "testtable") => ("dbo", "testtable", "[dbo].[testtable]", "'[dbo].[testtable]'")</item>
    /// </list>
    /// </summary>
    private async Task<(string schemaPureName, string tablePureName, string fullQuoteName, string fullNameForSeg)> FixTableOrViewName(string schemaPureName, string tablePureName)
    {
        //先确定 schema 名称
        var sql = string.Empty;
        if (schemaPureName.IsNullOrWhiteSpace())
        {
            sql =
                $"""
                select t.TABLE_SCHEMA,t.TABLE_NAME,t.TABLE_SCHEMA=SCHEMA_NAME() sort
                from INFORMATION_SCHEMA.TABLES t
                where t.TABLE_NAME={db.ProtectStringToSeg(tablePureName)}
                order by sort desc
                """;
        }
        else
        {
            sql =
                $"""
                select t.TABLE_SCHEMA,t.TABLE_NAME,1
                from INFORMATION_SCHEMA.TABLES t 
                where 
                    t.TABLE_NAME={db.ProtectStringToSeg(tablePureName)} 
                    and t.TABLE_SCHEMA={db.ProtectStringToSeg(schemaPureName)}
                """;
        }
        var res = await db.SelectModelAsync<(string schemaPureName, string tablePureName, int sort)>(sql);
        schemaPureName = res.schemaPureName;
        tablePureName = res.tablePureName;
        var fullQuoteName = $"{db.AddQuote(schemaPureName)}.{db.AddQuote(tablePureName)}";
        var fullNameForSeg = db.ProtectStringToSeg(fullQuoteName);
        return (schemaPureName, tablePureName, fullQuoteName, fullNameForSeg);
    }

    private async Task<(string schemaPureName, string tablePureName, string colPureName, string fullQuoteName, string fullNameForSeg)> FixColumnName(string schemaPureName, string tablePureName, string colPureName)
    {
        var sql = string.Empty;

        if (schemaPureName.IsNullOrWhiteSpace())
        {
            sql =
                $"""
                select t.TABLE_SCHEMA, t.TABLE_NAME,t.COLUMN_NAME,t.TABLE_SCHEMA=SCHEMA_NAME() sort
                from INFORMATION_SCHEMA.COLUMNS t
                where 
                    t.TABLE_NAME={db.ProtectStringToSeg(tablePureName)} 
                    and t.COLUMN_NAME={db.ProtectStringToSeg(colPureName)}
                order by sort desc
                """;
        }
        else
        {
            sql =
                $"""
                select t.TABLE_SCHEMA,t.TABLE_NAME,t.COLUMN_NAME,1 
                from INFORMATION_SCHEMA.COLUMNS t 
                where 
                    t.TABLE_NAME={db.ProtectStringToSeg(tablePureName)} 
                    and t.COLUMN_NAME={db.ProtectStringToSeg(colPureName)}
                    and t.TABLE_SCHEMA={db.ProtectStringToSeg(schemaPureName)}
                """;
        }
        var res = await db.SelectModelAsync<(string schemaPureName, string tablePureName, string colPureName, int sort)>(sql);
        var fullQuoteName = $"{db.AddQuote(res.schemaPureName)}.{db.AddQuote(res.tablePureName)}";
        var fullNameForSeg = db.ProtectStringToSeg(fullQuoteName);
        return (res.schemaPureName, res.tablePureName, res.colPureName, fullQuoteName, fullNameForSeg);
    }
    #endregion

    /// <inheritdoc />
    public override async Task<string> UpdateColumnUniqueSql(string tablePureName, string colPureName, bool isUnique, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tablePureName);
        AssertUtil.NotNullOrWhiteSpace(colPureName);

        (schemaPureName, tablePureName, colPureName, var fullQuoteName, var fullNameForSeg) = await FixColumnName(schemaPureName, tablePureName, colPureName);

        //先找历史的索引和约束
        var constraints = await GetConstraintsAsync(tablePureName, schemaPureName);
        var indexs = await GetIndexesAsync(tablePureName, schemaPureName);

        var sql = string.Empty;
        //查找唯一约束
        var cons = constraints.Where(i => i.ColumnName == colPureName && i.Type == EnumConstraintType.Unique && i.SchemaName == schemaPureName && i.TableName == tablePureName).ToList();
        if (cons.Count == 1)
        {
            var obj = cons.First();
            sql = $"""
                -- 需要先删除单独定义到此列上的唯一约束
                IF EXISTS (
                    select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                    where CONSTRAINT_NAME={db.ProtectStringToSeg(obj.Name)} and CONSTRAINT_SCHEMA={db.ProtectStringToSeg(obj.SchemaName)} 
                        and TABLE_NAME={db.ProtectStringToSeg(tablePureName)} and TABLE_SCHEMA={db.ProtectStringToSeg(schemaPureName)}
                )
                BEGIN
                    ALTER TABLE {fullQuoteName} DROP CONSTRAINT {db.AddQuote(obj.Name)};
                    PRINT '已删除唯一约束:'+{db.ProtectStringToSeg(obj.Name)};
                END
                """;
        }

        //找到唯一索引
        var indexes = indexs.Where(i => i.ColumnName == colPureName && i.TableName == tablePureName && i.SchemaName == schemaPureName && i.IsUnique).ToList();
        if (indexes.Count == 1)
        {
            var obj = indexes.First();
            sql += "\r\n";
            sql +=
                $"""
                -- 需要先删除单独定义在此列上的索引
                IF EXISTS (
                    SELECT 1 FROM sys.indexes 
                    WHERE name = {db.ProtectStringToSeg(obj.Name)} AND object_id = OBJECT_ID({fullNameForSeg})
                )
                BEGIN
                    DROP INDEX {db.AddQuote(obj.Name)} ON {fullQuoteName};
                    PRINT '已删除索引:'+{db.ProtectStringToSeg(obj.Name)};
                END
                """;
        }

        //再看是否要创建新的
        if (isUnique)
        {
            //创建约束即可, 会自动创建索引
            sql += "\r\n";
            //期待 表名和列名不会有特殊字符
            sql += $"ALTER TABLE {fullQuoteName} ADD CONSTRAINT UQ_{tablePureName}_{colPureName} UNIQUE ({db.AddQuote(colPureName)});";
        }

        return sql;
    }

    /// <inheritdoc />
    public override Task<string> UpdateColumnIdentitySql(string tablePureName, string colPureName, bool isIdentity, string schemaPureName = null)
    {
        return Task.FromResult("print '无法修改已有列是否自增, 如果想达到同样的效果, 可以考虑列迁移(增删列)'");
    }

    /// <inheritdoc />
    public override async Task<string> UpdateColumnTypeSql(string tablePureName, string colPureName, string typeString, string schemaPureName = null)
    {
        (schemaPureName, tablePureName, colPureName, string fullQuoteName, string fullNameForSeg) = await FixColumnName(schemaPureName, tablePureName, colPureName);
        return $"alter table {fullQuoteName} alter column {db.AddQuote(colPureName)} {typeString}";
    }

    /// <inheritdoc />
    public override async Task<string> UpdateColumnSql(string tablePureName, string colPureName, string schemaPureName = null)
    {
        (schemaPureName, tablePureName, colPureName, string fullQuoteName, string fullNameForSeg) = await FixColumnName(schemaPureName, tablePureName, colPureName);
        var column = (await GetColumnsAsync(tablePureName, schemaPureName, colPureName)).First();
        var sql = $"""
            -- 如果想修改列类型或是否可为空, 可参照:
            alter table {fullQuoteName} alter column {db.AddQuote(colPureName)} not null;

            -- 如果想修改列名, 可参照:
            exec sp_rename {db.AddQuote(schemaPureName)}.{db.AddQuote(tablePureName)}.{db.AddQuote(colPureName)}, 新列名, 'column';

            -- 如果想修改列注释, 可参照:
            {await UpdateColumnDescriptionSql(tablePureName, colPureName, "列描述", schemaPureName)}

            -- 如果想修改列是否唯一, 可参照:
            {await UpdateColumnUniqueSql(tablePureName, colPureName, true, schemaPureName)}

            -- 如果想修改列默认值, 可参照:
            {await UpdateColumnDefaultSql(tablePureName, colPureName, "0", schemaPureName)}

            """;
        return sql;
    }

    /// <inheritdoc />
    public override string UpdateViewDescriptionSql(string viewPureName, string description, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(viewPureName);
        var sql =
                $"""
                -- 传入 sp_updateextendedproperty 的 level0name 不能是函数调用
                DECLARE @CurrentSchema NVARCHAR(128) = {(schemaPureName.IsNullOrWhiteSpace() ? "SCHEMA_NAME()" : db.ConvertToSqlSeg(schemaPureName).Data)};
                -- 检查表是否已存在 MS_Description 属性 不检查直接调用 sp_updateextendedproperty 会报错
                IF EXISTS (
                    SELECT 1 
                    FROM sys.extended_properties 
                    WHERE name = N'MS_Description'
                      -- OBJECT_ID(N'testtable') or OBJECT_ID(N'[dbo].[testtable]')
                      AND major_id = OBJECT_ID({db.ConvertToSqlSeg(schemaPureName.IsNullOrWhiteSpace() ? viewPureName : (db.AddQuote(schemaPureName) + "." + db.AddQuote(viewPureName))).Data})
                      AND minor_id = 0  -- 表级属性（ minor_id=0 表示表本身）
                )
                BEGIN
                    -- 属性存在，更新
                    EXEC sp_updateextendedproperty 
                        @name = N'MS_Description',
                        @value = {db.ConvertToSqlSeg(description).Data},
                        @level0type = N'SCHEMA', @level0name = @CurrentSchema,
                        @level1type = N'VIEW', @level1name = {db.ConvertToSqlSeg(viewPureName).Data};
                END
                ELSE
                BEGIN
                    -- 属性不存在，添加
                    EXEC sp_addextendedproperty 
                        @name = N'MS_Description',
                        @value = {db.ConvertToSqlSeg(description).Data},
                        @level0type = N'SCHEMA', @level0name = @CurrentSchema,
                        @level1type = N'VIEW', @level1name = {db.ConvertToSqlSeg(viewPureName).Data};
                END
                -- 另外: 下面一行也可以用来查询表的注释
                -- SELECT * FROM fn_listextendedproperty ('MS_Description', 'schema', 'dbo', 'table', 'testtbl',null,null)
                -- 另外: 将表注释设为null不会有其他的出发逻辑, 纠正给正常的列设置一样
                """;
        return sql;
    }

    /// <summary>
    /// 修改函数说明
    /// </summary>
    public string UpdateFuncDescriptionSql(string funcPureName, string description, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(funcPureName);
        var sql =
                $"""
                -- 传入 sp_updateextendedproperty 的 level0name 不能是函数调用
                DECLARE @CurrentSchema NVARCHAR(128) = {(schemaPureName.IsNullOrWhiteSpace() ? "SCHEMA_NAME()" : db.ConvertToSqlSeg(schemaPureName).Data)};
                -- 检查表是否已存在 MS_Description 属性 不检查直接调用 sp_updateextendedproperty 会报错
                IF EXISTS (
                    SELECT 1 
                    FROM sys.extended_properties 
                    WHERE name = N'MS_Description'
                      -- OBJECT_ID(N'testtable') or OBJECT_ID(N'[dbo].[testtable]')
                      AND major_id = OBJECT_ID({db.ConvertToSqlSeg(schemaPureName.IsNullOrWhiteSpace() ? funcPureName : (db.AddQuote(schemaPureName) + "." + db.AddQuote(funcPureName))).Data})
                      AND minor_id = 0  -- 表级属性（ minor_id=0 表示表本身）
                )
                BEGIN
                    -- 属性存在，更新
                    EXEC sp_updateextendedproperty 
                        @name = N'MS_Description',
                        @value = {db.ConvertToSqlSeg(description).Data},
                        @level0type = N'SCHEMA', @level0name = @CurrentSchema,
                        @level1type = N'FUNCTION', @level1name = {db.ConvertToSqlSeg(funcPureName).Data};
                END
                ELSE
                BEGIN
                    -- 属性不存在，添加
                    EXEC sp_addextendedproperty 
                        @name = N'MS_Description',
                        @value = {db.ConvertToSqlSeg(description).Data},
                        @level0type = N'SCHEMA', @level0name = @CurrentSchema,
                        @level1type = N'FUNCTION', @level1name = {db.ConvertToSqlSeg(funcPureName).Data};
                END
                -- 另外: 下面一行也可以用来查询表的注释
                -- SELECT * FROM fn_listextendedproperty ('MS_Description', 'schema', 'dbo', 'table', 'testtbl',null,null)
                -- 另外: 将表注释设为null不会有其他的出发逻辑, 纠正给正常的列设置一样
                """;
        return sql;
    }

    /// <summary>
    /// 修改存储过程说明
    /// </summary>
    public string UpdateProcDescription(string procPureName, string description, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(procPureName);
        var sql =
                $"""
                -- 传入 sp_updateextendedproperty 的 level0name 不能是函数调用
                DECLARE @CurrentSchema NVARCHAR(128) = {(schemaPureName.IsNullOrWhiteSpace() ? "SCHEMA_NAME()" : db.ConvertToSqlSeg(schemaPureName).Data)};
                -- 检查表是否已存在 MS_Description 属性 不检查直接调用 sp_updateextendedproperty 会报错
                IF EXISTS (
                    SELECT 1 
                    FROM sys.extended_properties 
                    WHERE name = N'MS_Description'
                      -- OBJECT_ID(N'testtable') or OBJECT_ID(N'[dbo].[testtable]')
                      AND major_id = OBJECT_ID({db.ConvertToSqlSeg(schemaPureName.IsNullOrWhiteSpace() ? procPureName : (db.AddQuote(schemaPureName) + "." + db.AddQuote(procPureName))).Data})
                      AND minor_id = 0  -- 表级属性（ minor_id=0 表示表本身）
                )
                BEGIN
                    -- 属性存在，更新
                    EXEC sp_updateextendedproperty 
                        @name = N'MS_Description',
                        @value = {db.ConvertToSqlSeg(description).Data},
                        @level0type = N'SCHEMA', @level0name = @CurrentSchema,
                        @level1type = N'PROCEDURE', @level1name = {db.ConvertToSqlSeg(procPureName).Data};
                END
                ELSE
                BEGIN
                    -- 属性不存在，添加
                    EXEC sp_addextendedproperty 
                        @name = N'MS_Description',
                        @value = {db.ConvertToSqlSeg(description).Data},
                        @level0type = N'SCHEMA', @level0name = @CurrentSchema,
                        @level1type = N'PROCEDURE', @level1name = {db.ConvertToSqlSeg(procPureName).Data};
                END
                -- 另外: 下面一行也可以用来查询表的注释
                -- SELECT * FROM fn_listextendedproperty ('MS_Description', 'schema', 'dbo', 'table', 'testtbl',null,null)
                -- 另外: 将表注释设为null不会有其他的出发逻辑, 纠正给正常的列设置一样
                """;
        return sql;
    }

    /// <summary>
    /// 修改列说明
    /// </summary>
    public string UpdateViewColumnDescription(string viewPureName, string colPureName, string description, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(viewPureName);
        AssertUtil.NotNullOrWhiteSpace(colPureName);
        var sql =
                $"""
                -- 传入 sp_updateextendedproperty 的 level0name 不能是函数调用
                DECLARE @CurrentSchema NVARCHAR(128) = {(schemaPureName.IsNullOrWhiteSpace() ? "SCHEMA_NAME()" : db.ConvertToSqlSeg(schemaPureName).Data)};
                -- 检查表是否已存在 MS_Description 属性 不检查直接调用 sp_updateextendedproperty 会报错
                IF EXISTS (
                    SELECT * FROM fn_listextendedproperty ('MS_Description', 'schema', {(schemaPureName.IsNullOrWhiteSpace() ? "SCHEMA_NAME()" : db.ConvertToSqlSeg(schemaPureName).Data)}, 'VIEW', {db.ConvertToSqlSeg(viewPureName).Data},'column',{db.ConvertToSqlSeg(colPureName)})
                )
                BEGIN
                    -- 属性存在，更新
                    EXEC sp_updateextendedproperty 
                        @name = N'MS_Description',
                        @value = {db.ConvertToSqlSeg(description).Data},
                        @level0type = N'SCHEMA', @level0name = @CurrentSchema,
                        @level1type = N'VIEW', @level1name = {db.ConvertToSqlSeg(viewPureName).Data}
                        @level2type = N'COLUMN', @level2name = {db.ConvertToSqlSeg(colPureName).Data};
                END
                ELSE
                BEGIN
                    -- 属性不存在，添加
                    EXEC sp_addextendedproperty 
                        @name = N'MS_Description',
                        @value = {db.ConvertToSqlSeg(description).Data},
                        @level0type = N'SCHEMA', @level0name = @CurrentSchema,
                        @level1type = N'VIEW', @level1name = {db.ConvertToSqlSeg(viewPureName).Data}
                        @level2type = N'COLUMN', @level2name = {db.ConvertToSqlSeg(colPureName).Data};
                END
                -- 另外: 将表注释设为null不会有其他的出发逻辑, 纠正给正常的列设置一样
                """;
        return sql;
    }

    //private Result updateExtendedProperty(string schemaName, string level1type, string level1name, string level2type, string level2name, string propName, string propValue)
    //{
    //    return Result.Wrap(() =>
    //    {
    //        level2type = (level2type == null ? "null" : "'" + level2type + "'");
    //        level2name = (level2name == null ? "null" : "'" + level2name + "'");
    //        string sql = $"SELECT count(1) FROM fn_listextendedproperty ('{propName}', 'schema', '{schemaName}', '{level1type}', '{level1name}',{level2type},{level2name});";
    //        string sql2 = "";
    //        if (db.SelectScalar<string>(sql) == "1")
    //        {
    //            sql2 = $"EXEC sp_dropextendedproperty @name=N'{propName}', @level0type=N'SCHEMA',@level0name='{schemaName}', @level1type=N'{level1type}',@level1name=N'{level1name}', @level2type={level2type},@level2name={level2name};\r\n";
    //        }
    //        sql2 += $"EXEC sys.sp_addextendedproperty @name = N'{propName}', @value = N'{propValue}' , @level0type = N'SCHEMA',@level0name = '{schemaName}', @level1type = N'{level1type}',@level1name = N'{level1name}', @level2type = {level2type},@level2name = {level2name};";

    //        db.ExecuteSql(sql2);
    //    });
    //}

    /// <summary>
    /// 获取指定表、指定列的默认约束名称
    /// </summary>
    private async Task<string> GetColumnDefaultConstraintAsync(string tablePureName, string colPureName, string schemaPureName = null)
    {
        (schemaPureName, tablePureName, colPureName, var fullQuoteName, var fullNameForSeg) = await FixColumnName(schemaPureName, tablePureName, colPureName);
        // 不指定schema搜索默认约束
        string sql = $@"
select name from sys.objects obj 
where 
obj.object_id=(
	select sysc.default_object_id 
	from sys.columns sysc 
	where 
		sysc.name={db.ProtectStringToSeg(colPureName)} 
		and 
		sysc.object_id=
			(select object_id 
				from sys.objects 
				where name={db.ProtectStringToSeg(tablePureName)}
			)
)";
        if (schemaPureName.IsNotNullOrWhiteSpace())
        {
            // 指定schema搜索默认约束
            sql = $@"
select name from sys.objects obj 
where 
obj.object_id=(
	select sysc.default_object_id 
	from sys.columns sysc 
	where 
		sysc.name={db.ProtectStringToSeg(colPureName)}
		and 
		sysc.object_id=
			(select object_id 
				from sys.objects 
				where name={db.ProtectStringToSeg(tablePureName)}
				and schema_id=(select schema_id from sys.schemas where name={db.ProtectStringToSeg(schemaPureName)}
			)
)";
        }
        return await db.SelectScalarAsync<string>(sql);
    }

    /// <inheritdoc />
    public override async Task<string> UpdateColumnDefaultSql(string tablePureName, string colPureName, string defaultString, string schemaPureName = null)
    {
        (schemaPureName, tablePureName, colPureName, var fullQuoteName, var fullNameForSeg) = await FixColumnName(schemaPureName, tablePureName, colPureName);
        string defname = await GetColumnDefaultConstraintAsync(tablePureName, colPureName, schemaPureName);
        var sql = "";
        if (defname.IsNotNullOrWhiteSpace())
        {
            sql += $"ALTER TABLE {fullQuoteName} DROP CONSTRAINT {db.AddQuote(defname)};";
        }
        if (defaultString != null)
        {
            sql += $"\r\nALTER TABLE {fullQuoteName} ADD CONSTRAINT DF_gene_{tablePureName}_{colPureName} DEFAULT ('{defaultString}') FOR {db.AddQuote(colPureName)}";
        }
        return sql;
    }

    /// <inheritdoc />
    public override async Task<string> RemoveColumnDefaultSql(string tablePureName, string colPureName, string schemaPureName = null)
    {
        (schemaPureName, tablePureName, colPureName, var fullQuoteName, var fullNameForSeg) = await FixColumnName(schemaPureName, tablePureName, colPureName);
        string defname = await GetColumnDefaultConstraintAsync(tablePureName, colPureName, schemaPureName);
        var sql = "";
        if (defname.IsNotNullOrWhiteSpace())
        {
            sql = $"ALTER TABLE {fullQuoteName} DROP CONSTRAINT {db.AddQuote(defname)};";
        }
        else
        {
            sql = "print('没有默认约束, 无法删除')";
        }
        return sql;
    }

    /// <inheritdoc />
    public override string DropTriggerSql(string triggerPureName, string schemaPureName = null)
    {
        var sql = $"IF EXISTS(SELECT * FROM sys.triggers WHERE name = {db.ProtectStringToSeg(triggerPureName)} drop trigger {db.AddQuote(triggerPureName)}; ";
        if (schemaPureName.IsNotNullOrWhiteSpace())
        {
            sql = $"IF EXISTS(SELECT * FROM sys.triggers WHERE name = {db.ProtectStringToSeg(triggerPureName)} and SCHEMA_ID({db.ProtectStringToSeg(schemaPureName)})=(select schema_id from sys.objects syso where syso.object_id= parent_id)) drop trigger {db.AddQuote(schemaPureName)}.{db.AddQuote(triggerPureName)}; ";
        }
        return sql;
    }

    /// <inheritdoc />
    public override string UpdateConstaintSql() => string.Empty;

    /// <inheritdoc />
    public override string UpdateIndexSql() => string.Empty;

    /// <inheritdoc />
    public override string UpdateTriggerSql() => string.Empty;

    public override string SetPrimaryKeySql(string tablePureName, string schemaPureName = null, params string[] colPureNames)
    {
        AssertUtil.NotNullOrWhiteSpace(tablePureName);
        if (schemaPureName.IsNullOrWhiteSpace())
        {
            tablePureName = db.AddQuote(tablePureName);
        }
        else
        {
            tablePureName = $"{db.AddQuote(schemaPureName)}.{db.AddQuote(tablePureName)}";
        }
        AssertUtil.NotNullOrEmpty(colPureNames);

        var sql = $@"-- 设置表主键就是给表增加主键约束,mysql中约束名字一般为:`PRIMARY`
-- 注意: 需要先删除现有的主键约束: alter table {tablePureName} drop constraint `PRIMARY`
alter table {tablePureName} add constraint `PRIMARY` primary key({colPureNames.Select(i => db.AddQuote(i)).ToStringSeparated(",")})
";
        return sql;
    }

    public override string RemovePrimaryKeySql(string tablePureName, string schemaPureName = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tablePureName);
        if (schemaPureName.IsNullOrWhiteSpace())
        {
            tablePureName = db.AddQuote(tablePureName);
        }
        else
        {
            tablePureName = $"{db.AddQuote(schemaPureName)}.{db.AddQuote(tablePureName)}";
        }
        var sql = $@"-- 删除主键就是删除表上的主键约束(主键索引自动删除),mysql中约束名字一般为:`PRIMARY`
alter table {tablePureName} drop constraint `PRIMARY`
";
        return sql;
    }
    #endregion
}
